<?php
	require_once('../../lib/'.'BaseDir.inc.htm');
	require_once(EIU_LIB.'DBCommon.class.htm');
	//require_once (EIU_CLASS.'BaseClass.htm');
	
	/*
		@author: 	Hien Huynh
		@des:		Hàm dùng để insert dữ liệu vào table staff_tbl
		@date:		06/12/2011
		@param: 	$email
		@return:	true|false
	*/
	
	function insertStaffInfo($arrUser){
		$oDB = new DBCommon();
		$conn =  $oDB->stl_openConn();
		$today = date("Y-m-d h:i:s");
		
		$q_user = "INSERT INTO `staff_tbl` (`StaffID`,`FirstName`, `MiddleName`, `LastName`,`FullName`, `BirthDate`, `BirthMonth`, `BirthYear`, `DepartmentID`, `Type`, `SchoolEmail`, `Address`, `Phone`, `Mobile`, `ExpireDateEmail`, `CreateDate`, `isDeleted`, `LastUpdate`, `ModifyBy`,`isStaff`) VALUES ('".$arrUser['code']."','".$arrUser['firstName']."', '".$arrUser['middleName']."', '".$arrUser['lastName']."', '".$arrUser['fullname']."','".$arrUser['dateBirth']."', '".$arrUser['monthBirth']."', '".$arrUser['yearBirth']."', '".$arrUser['departmentID']."', '2', '".$arrUser['email']."', NULL, NULL, NULL, NULL, '$today', '', CURRENT_TIMESTAMP, '',".$arrUser['isStaff'].");";
	//	print_r($q_user);
		$rs_insert = $oDB->stl_db_change($q_user, $conn);
		return $rs_insert;
	//	print_r($rs_insert);
	//	exit();
		$oDB->stl_closeConn($conn);
	}
	
	/*
		@author: 	Hien Huynh
		@des:		Hàm dùng để update dữ liệu vào table staff_tbl
		@date:		06/12/2011
		@param: 	$email
		@return:	true|false
	*/
	
	function updateStaffInfo($arrUser){
		//print_r($arrUser);exit();
		$oDB = new DBCommon();
		$conn =  $oDB->stl_openConn();
		$today = date("Y-m-d h:i:s");
		$modifyby = 'Hiền Huỳnh';
		if(isset($arrUser['FirstName'])&&!empty($arrUser['FirstName'])){
			$arrUser['FullName'] = $arrUser['FirstName'];
		}
		if(isset($arrUser['MiddleName'])&&!empty($arrUser['MiddleName'])){
			$arrUser['FullName'] .= " ".$arrUser['MiddleName'];
		}
		
		if(isset($arrUser['LastName'])&&!empty($arrUser['LastName'])){
			$arrUser['FullName'] .= " ".$arrUser['LastName'];
		}
		
		$q_user = "UPDATE `staff_tbl` 
				SET `Passport` = '".$arrUser['Passport']."', `FirstName` = '".$arrUser['FirstName']."', `MiddleName` = '".$arrUser['MiddleName']."', 
				`LastName` = '".$arrUser['LastName']."', `FullName` = '".$arrUser['FullName']."', `Gender` = '".$arrUser['Gender']."', 
				`BirthDate` = '".$arrUser['BirthDate']."', `BirthMonth` = '".$arrUser['BirthMonth']."', `BirthYear` = '".$arrUser['BirthYear']."', 
				`PrivateEmail` = '".$arrUser['PrivateEmail']."', `Nationality` = '".$arrUser['Nationality']."', 
				`DepartmentID` = '".$arrUser['DepartmentID']."', `Type` = '".$arrUser['Type']."', `Phone` = '".$arrUser['Phone']."', 
				`Mobile` = '".$arrUser['Mobile']."', `Address` = '".$arrUser['Address']."', 
				`City` = '".$arrUser['City']."', `CityCode` = '".$arrUser['CityCode']."'";
		if(!empty($arrUser['ImageName'])){
			$q_user .= ",`ImagePath` = '".$arrUser['ImageName']."'";
		}
		$q_user .= ",`isDeleted` = '".$arrUser['isDeleted']."', `ModifyBy` = '".$modifyby."' 
				WHERE `staff_tbl`.`StaffID` = '".$arrUser['StaffID']."' LIMIT 1;";

				
		//print_r($q_user);exit();
		$rs_update = $oDB->stl_db_change($q_user, $conn);
		return $rs_update;
		$oDB->stl_closeConn($conn);
	}
	
	
	/*
		@author: 	Hien Huynh
		@des:		Hàm dùng để kiểm tra email và staffID đã tồn tại chưa
		@date:		02/12/2011
		@param: 	$email,$staffID
		@return:	true|false
	*/
	
	function checkExistStaff($email,$code){
		$oDB = new DBCommon();
		$conn =  $oDB->stl_openConn();
		
		$q_select = "SELECT count(*) as count FROM `staff_tbl` WHERE `SchoolEmail` LIKE '$email' AND `StaffID`='$code' AND `isDeleted`=0";
	
		$rs_select = $oDB->stl_db_select($q_select, $conn);

		if($rs_select[0]->count<1){
			return false;
		}else{
			return true;
		}
		
		$oDB->stl_closeConn($conn);
	}
	
	/*
		@author: 	Hien Huynh
		@des:		Hàm dùng để kiểm tra email đã tồn tại chưa
		@date:		02/12/2011
		@param: 	$email
		@return:	true|false
	*/
	
	function checkExistEmail($s_value,$email){
		$oDB = new DBCommon();
		$conn =  $oDB->stl_openConn();
		
		//Tạo mới email
		if(is_array($s_value)){
			$email1 = $email."@eiu.edu.vn";
		}else{//kiểm tra email tồn tại
			$email1 = $s_value;
			$email = $email1;
		}
		
		$q_select = "SELECT `SchoolEmail` FROM `staff_tbl` WHERE `SchoolEmail` LIKE '%$email%'";
	//	print_r($q_select);
		$rs_select = $oDB->stl_db_select($q_select, $conn);
		//print_r($rs_select);
		if(count($rs_select) < 1){
			return $email1;
		}else{
			if(is_array($s_value) && is_array($rs_select)){
				foreach($rs_select as $key=>$value){
					$arrEmail[$key] = $rs_select[$key]->SchoolEmail;
				}
				
				if(array_search($email1, $arrEmail) !== false){
					$middleName = strtolower(changeTVToEnglish($s_value['middleName']));
					$middleName = str_replace(" ", "", $middleName);
					$email2 = $email.$middleName."@eiu.edu.vn";
					
					if(array_search($email2, $arrEmail) !== false){
						$yearBirth = $s_value['yearBirth'];
						$monthBirth = $s_value['monthBirth'];
						$dateBirth = $s_value['dateBirth'];
						
						$email3 = $email.$middleName.$yearBirth."@eiu.edu.vn";
						if(array_search($email3, $arrEmail) !== false){
							$email4 = $email.$middleName.$yearBirth.$monthBirth."@eiu.edu.vn";
							if(array_search($email4, $arrEmail) !== false){
								$email5 = $email.$middleName.$yearBirth.$monthBirth.$dateBirth."@eiu.edu.vn";
								if(array_search($email5, $arrEmail) !== false){
									return 'NG';
								}else{
									return $email5;
								}
							}else{
								return $email4;
							}
						}else{
							return $email3;
						}
					}else{
					//	echo "hien";
						return $email2;
					}
				}else{
					return $email1;
				}
			}else{
				return 'NG';
			}
		}
		
		$oDB->stl_closeConn($conn);
	}
	
	/*
		@author: 	Hien Huynh
		@des:		Hàm dùng để lưu thông tin đọc từ file Excel xuống database, không trả về 
					các row có đơn vị rỗng
		@date:		02/12/2011
		@param: 	$data: dữ liệu đọc từ file Excel user uploaded
		@return:	OK|NG
	*/
	function importDataToDB($data,$columnUnit){
		$oDB = new DBCommon();
		$conn =  $oDB->stl_openConn();
	
		$arrData='';
	//	print_r($data);
		//Duyệt số file import vào
		foreach($data as $key=>$value){
			$arrCreateEmail = '';
			$arrResetEmail = '';
			//Duyệt số dòng trong file import vào
			foreach($value as $s_key=>$s_value){
				//$s_value['fullname'] = '';
				//Check department not null
				if(isset($s_value[$columnUnit]) && !empty($s_value[$columnUnit])){
					//Check create a new email or reset email
					$code = $s_value[1];//mã nhân viên
					$email = $s_value[9];//email trường
				//	print_r(checkExistStaff($email,$code));
					//case 1: code and email are not null->reset email for private user
					if(!empty($email)&&empty($code)){
						$email = checkExistEmail($s_value[9]);
						// case: email not exist-> create a new public email
						$s_value['isPublicEmail'] = 1;
						if($email!='NG'){
							$arrCreateEmail[$s_key] = $s_value;
						}else{//case: email existed -> reset email public
							$arrResetEmail[$s_key] = $s_value;
						}
					}else if(!empty($email)&&!empty($code)){
						if(checkExistStaff($email,$code)==true){
							$arrResetEmail[$s_key] = $s_value;//reset email staff
						}
					}else if(empty($code)&&empty($email)){//case: code is null and email is null->create a new email staff
						$arrCreateEmail[$s_key] = $s_value;
					}
				}
			}
			$arrData[$key]['createEmail'] = $arrCreateEmail;
			$arrData[$key]['resetEmail'] = $arrResetEmail;
		}
		
	//	print_r($arrData);exit();
		return $arrData;
		$oDB->stl_closeConn($conn);
	}
	
	function createNewEmail($s_value){
		$oDB = new DBCommon();
		$conn =  $oDB->stl_openConn();
		
	//	print_r($s_value);
	//	print_r($s_value);
		//Kiểm tra tạo email chung hay email cá nhân: check column email
		if(isset($s_value['isPublicEmail']) && $s_value['isPublicEmail']==1){
			$email = checkExistEmail($s_value['email']);
			
			if($email != 'NG'){
				//Tạo mã
				//$q_select = "SELECT COUNT( * ) as count FROM  `staff_tbl` WHERE `isStaff`=2 AND `StaffID` LIKE '".$arrUser['departmentCode']."_%'";
				$q_select = "SELECT SUBSTRING(MAX(`StaffID`),3,4) as code FROM `staff_tbl` WHERE `isStaff`=2 AND `StaffID` LIKE '".$arrUser['departmentCode']."_%'";
				$rs_select = $oDB->stl_db_select($q_select, $conn);
				$code = (float)($rs_select[0]->code);
				if($code >= 0 && $code < 9){
					$code = "_00".($code+1);
				}else if($code >= 9 && $code < 99){
					$code = "_0".($code+1);
				}else{
					$code = $code+1;
				}
				
				$s_value['code'] = $s_value['departmentCode'].$code;
				$s_value['isStaff'] = 2;
				if(insertStaffInfo($s_value)=='OK'){
					return $s_value;
				}
			}else{
				//$arrRowNotImport[$key][$s_key] = $s_key;
			}
		}else{
			$firstName = strtolower(changeTVToEnglish($s_value['firstName']));
			$lastName = strtolower(changeTVToEnglish($s_value['lastName']));

			$name = $firstName.".".$lastName;
			//validate FirstName, LastName, DateBirth, MonthBirth, YearBirth, DepartmentID
			if(isset($s_value['firstName']) && !empty($s_value['firstName']) && isset($s_value['lastName']) && !empty($s_value['lastName']) && isset($s_value['yearBirth']) && !empty($s_value['yearBirth']) && isset($s_value['department']) && !empty($s_value['department'])){
				$email = checkExistEmail($s_value, $name);
			//	print_r($email);
				$s_value['email'] = $email;
				
				$s_value['fullname'] = $s_value['lastName'];
				if(!empty($s_value['middleName'])){
					$s_value['fullname'] .= " ".$s_value['middleName'];
				}
				$s_value['fullname'] .= " ".$s_value['firstName'];

				if(!empty($email) && $email != 'NG'){
					//Tạo mã
					$q_select = "SELECT SUBSTRING(MAX(`StaffID`),3,4) as code FROM  `staff_tbl` WHERE `isStaff`<>2 AND `StaffID` LIKE '".$s_value['departmentCode']."_%'";
					$rs_select = $oDB->stl_db_select($q_select, $conn);
				//	print_r($q_select);
				//	print_r($rs_select);
					$code = (float)($rs_select[0]->code);
					if($code >= 0 && $code < 9){
						$code = "000".($code+1);
					}else if($code >= 9 && $code < 99){
						$code = "00".($code+1);
					}else if($code >= 99 && $code < 999){
						$code = "000".($code+1);
					}else{
						$code = $code+1;
					}
				//	print_r($code);exit();
					$s_value['code'] = $s_value['departmentCode'].$code;
					$s_value['isStaff'] = 0;
					
					if(insertStaffInfo($s_value)=='OK'){
						return $s_value;
					}
				}else if($email == 'NG'){
					//$arrRowNotImport[$key][$s_key] = $s_key;
				}
			}
		}
		
		return 'NG';
	}
	
	function loadListImportedRows($data){
		//print_r($data);
		//Load data import database
		$oDB = new DBCommon();
		$conn =  $oDB->stl_openConn();
		
		$conditions = "";
		if(isset($data['userID']) && !empty($data['userID'])){
			$conditions .= " AND s.`Code` LIKE '%".$data['userID']."%'";
		}
		
		if(isset($data['userName']) && !empty($data['userName'])){
			$conditions .= " AND s.`FullName` LIKE '%".$data['userName']."%'";
		}
		
		if(isset($data['fromdate']) && !empty($data['fromdate'])){
			$fromdate = convertDate($data['fromdate'], "/");
			$conditions .= " AND DATE(s.`CreateDate`) >= '".$fromdate."'";
		}
		
		if(isset($data['todate']) && !empty($data['todate'])){
			$todate = convertDate($data['todate'], "/");
			$conditions .= " AND DATE(s.`CreateDate`) <= '".$todate."'";
		}
		
		if(isset($data['department']) && !empty($data['department'])){
			$conditions .= " AND s.`DepartmentID` = ".$data['department'];
		}
		
		$q_sel = "SELECT s.`RecordID`,s.`Code`,d.`FullName`,s.`FirstName`,s.`MiddleName`,s.`LastName`,s.`DateBirth`,s.`MonthBirth`,s.`YearBirth`,s.`EmailSchool`,s.`DepartmentID` FROM `staffinfo` as s LEFT JOIN `department` as d ON(s.DepartmentID=d.RecordID) WHERE s.`isDeleted`=0".$conditions;
	//	print_r($q_sel);
		$rs_sel = $oDB->stl_db_select($q_sel, $conn);
	//	print_r($rs_sel);
		$data2 = "";
		foreach($rs_sel as $key=>$val){
			$data2[$key]['HoLotVN'] = $rs_sel[$key]->LastName;
			if(!empty($rs_sel[$key]->MiddleName)){
				$data2[$key]['HoLotVN'] .= " ".$rs_sel[$key]->MiddleName;
			}
			$data2[$key]['TenVN'] .= " ".$rs_sel[$key]->FirstName;
			
			$data2[$key]['Birthday'] = $rs_sel[$key]->DateBirth."/".$rs_sel[$key]->MonthBirth."/".$rs_sel[$key]->YearBirth;
			if($rs_sel[$key]->YearBirth==0){
				$data2[$key]['Birthday']='';
			}
			
			$data2[$key]['Email'] = $rs_sel[$key]->EmailSchool;
			$data2[$key]['DepartmentID'] = $rs_sel[$key]->DepartmentID;
			$data2[$key]['Department'] = $rs_sel[$key]->FullName;
			$data2[$key]['Code'] = $rs_sel[$key]->Code;
			$data2[$key]['RecordID'] = $rs_sel[$key]->RecordID;
		}
		
		return $data2;
		
		$oDB->stl_closeConn($conn);
	}
	
	function getValue($table,$field,$condition){
		$oDB = new DBCommon();
		$conn =  $oDB->stl_openConn();
		
		$q_select = "SELECT $field FROM `$table` WHERE $condition";
		$rs_select = $oDB->stl_db_select($q_select, $conn);
		
		return $rs_select[0];
		$oDB->stl_closeConn($conn);
	}
	
	function insertFileUpload($arrUser){
		$oDB = new DBCommon();
		$conn =  $oDB->stl_openConn();
		$today = date("Y-m-d h:i:s");
		$modifyBy = '';
		$q_user = "INSERT INTO `upload_tbl` (`StaffID`,`FileName`, `Description`, `CreateDate`, `isDeleted`, `LastUpdate`, `ModifyBy`) VALUES ('".$arrUser['StaffID']."','".$arrUser['FileName']."', '".$arrUser['Description']."','$today',0, CURRENT_TIMESTAMP,'$modifyBy');";
		//print_r($q_user);
		$rs_insert = $oDB->stl_db_change($q_user, $conn);
		//print_r($rs_insert);exit();
		return $rs_insert;
	//	print_r($rs_insert);
	//	exit();
		$oDB->stl_closeConn($conn);
	}
	
	function updateFileUpload($arrUser){
		$oDB = new DBCommon();
		$conn =  $oDB->stl_openConn();
		$today = date("Y-m-d h:i:s");
		$modifyBy = '';
		$q_user = "UPDATE `upload_tbl` 
				SET `LastUpdate`=CURRENT_TIMESTAMP,`ModifyBy`='$modifyBy',`Description`='".$arrUser['Description']."'
				WHERE `StaffID`='".$arrUser['StaffID']."' AND `FileName`='".$arrUser['FileName']."'";
		//print_r($q_user);
		$rs_update = $oDB->stl_db_change($q_user, $conn);
		
		return $rs_update;

		$oDB->stl_closeConn($conn);
	}
	
	function checkExistFile($FileName,$StaffID){
		$oDB = new DBCommon();
		$conn =  $oDB->stl_openConn();
		
		$q_select = "SELECT count(*) as count FROM `upload_tbl` WHERE `FileName`='$FileName' AND `StaffID`='$StaffID' AND `isDeleted`=0";
	
		$rs_select = $oDB->stl_db_select($q_select, $conn);

		if($rs_select[0]->count<1){
			return false;
		}else{
			return true;
		}
		
		$oDB->stl_closeConn($conn);
	}
?>